﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using BaseWordStat;

namespace DbLayer
{
    public static class AdoProxy
    {
        public static void AddArticleDb(Article article)
        {
            using (var con = CreateConnection())
            {
                con.Open();

                int authorId = AddAuthor(article.Author, con);

                if (authorId == -1)
                    return;

                var articleId = AddArticle(article.Title, article.Date, article.Text, article.Url, article.Source, con);

                if (articleId == -1)
                    return;

                AddAuthorArticle(articleId, authorId, con);

                var words = DbProxy.PrepareWords(article.Words);

                var dbWords = AddWords(words.Keys, con);

                AddArticleWords(articleId, dbWords, words, con);
            }
        }

        private static void AddArticleWords(int articleId, List<Word> dbWords, IDictionary<string, int> words, DbConnection con)
        {
            foreach (var dbWord in dbWords)
            {
                if (!words.ContainsKey(dbWord.WordText))
                    continue;

                AddArticleWordRecord(articleId, dbWord, words[dbWord.WordText], con);
            }
        }

        private static void AddArticleWordRecord(int articleId, Word dbWord, int count, DbConnection con)
        {
            var c = CreateCommand(con);
            c.CommandText = "INSERT INTO ArticleWords(ArticleId, WordId, WordCount) VALUES(@ArticleId, @WordId, @WordCount);";

            AddParameter(c, "@ArticleId", articleId.ToString());
            AddParameter(c, "@WordId", dbWord.WordId.ToString());
            AddParameter(c, "@WordCount", count);

            c.ExecuteNonQuery();
        }

        private static List<Word> AddWords(ICollection<string> keys, DbConnection con)
        {
            return keys.Select(key => AddWord(key, con)).Where(word => word != null).ToList();
        }

        private static Word AddWord(string word, DbConnection con)
        {
            var resWord = FindWord(con, word);

            if (resWord != null)
                return resWord;

            return AddWordToDb(word, con);
        }

        private static Word AddWordToDb(string word, DbConnection con)
        {
            if (word.Length > 49)
                return null;

            var cmd = CreateCommand(con);

            cmd.CommandText = "INSERT INTO Words (WordText) VALUES (@WordText)";

            AddParameter(cmd, "@WordText", word);

            cmd.ExecuteNonQuery();

            return FindWord(con, word);
        }

        private static Word FindWord(DbConnection con, string word)
        {
            var cmd = CreateCommand(con);
            cmd.CommandText = "SELECT WordId From Words WHERE WordText = @WordText";

            AddParameter(cmd, "@WordText", word);

            var reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                int id;

                if (int.TryParse(reader[0].ToString(), out id))
                    return new Word() { WordId = id, WordText = word };

                break;
            }

            return null;
        }

        private static void AddAuthorArticle(int articleId, int authorId, DbConnection con)
        {
            var c = CreateCommand(con);
            c.CommandText = "INSERT INTO AuthorArticles(ArticleId, AuthorId) VALUES(@ArticleId, @AuthorId);";
            AddParameter(c, "@ArticleId", articleId.ToString());
            AddParameter(c, "@AuthorId", authorId.ToString());
            c.ExecuteNonQuery();
        }

        private static int AddArticle(string title, DateTime date, string text, string url, string source, DbConnection con)
        {
            var query =
                "INSERT INTO Articles(ArticleTitle, ArticleUrl, ArticleDate, ArticleText, Source) VALUES(@ArticleTitle, @ArticleUrl, @ArticleDate, @ArticleText, @Source);" +
                "SELECT ArticleId FROM Articles WHERE ArticleId = @@IDENTITY";

            var command = CreateCommand(con);
            command.CommandText = query;

            AddParameter(command, "@ArticleTitle", title);
            AddParameter(command, "@ArticleUrl", url);
            AddParameter(command, "@ArticleDate", date);
            AddParameter(command, "@ArticleText", text);
            AddParameter(command, "@Source", source);

            var obj = command.ExecuteScalar();

            if (obj == null)
                return -1;

            int id;

            if (int.TryParse(obj.ToString(), out id))
                return id;

            return -1;
        }

        private static int AddAuthor(string author, DbConnection con)
        {
            var id = GetAuthorByName(con, author);

            if (id != -1)
                return id;

            InsertAuthor(con, author);

            return GetAuthorByName(con, author);
        }

        private static void InsertAuthor(DbConnection con, string author)
        {
            var insertCommand = CreateCommand(con);

            insertCommand.CommandText = "INSERT INTO Authors(NameSurname) VALUES (@NameSurname)";

            AddParameter(insertCommand, "@NameSurname", author);
            insertCommand.ExecuteScalar();
        }

        private static int GetAuthorByName(DbConnection con, string author)
        {
            var queryString = "SELECT AuthorId FROM Authors where NameSurname= @Name";

            var command = CreateCommand(con);

            command.CommandText = queryString;

            AddParameter(command, "@Name", author);

            var reader = command.ExecuteReader();

            while (reader.Read())
            {
                int id;

                if (int.TryParse(reader[0].ToString(), out id))
                    return id;

                break;
            }

            return -1;
        }

        private static DbConnection CreateConnection()
        {
            return new SqlConnection
                          {
                              ConnectionString = @"Data Source=KAPATICH\SqlExpress;Initial Catalog=TestDb;Integrated Security=True;MultipleActiveResultSets=True"
                          };
        }

        private static DbCommand CreateCommand(DbConnection con)
        {
            return new SqlCommand { Connection = con as SqlConnection };
        }

        private static void AddParameter(DbCommand com, string parName, object parVal)
        {
            var sqlCom = com as SqlCommand;

            if (sqlCom == null)
                return;

            sqlCom.Parameters.AddWithValue(parName, parVal);
        }
    }

    internal class Word
    {
        public int WordId { get; set; }

        public string WordText { get; set; }
    }
}
